
/*
*
*	Primera Pregunta
*	1.  Crear una Consulta que liste todos los alumnos aprobados por Carrera. 
*	(Nombre de la Carrera y Nombre Completo del Alumno)
*/
SELECT cr.Nombre AS Carrera
	, COALESCE((p.Nombre+' '+p.SegundoNombre+' '+p.ApellidoPaterno+' '+p.ApellidoMaterno)
		,(p.Nombre+' '+p.ApellidoPaterno+' '+p.ApellidoMaterno)) AS 'Alumno'
	FROM Cursa c
		JOIN Persona p ON p.Codigo = c.CodigoAlumno
		JOIN Materia m ON m.Id = c.IdMateria
		JOIN Carrera cr ON cr.Id = m.IdCarrera
	WHERE	c.NotaFinal>50

/*
*
*	Segunda Pregunta
*	2. Crear una consulta de los 2 mejores almunos y los 2 peores alumnos por Carrera 
*	(Nombre de la Carrera, Nombre completo del alumno, Nota, una Columna que diga Aprobado/Reprobado según el caso)
*/
;WITH ma AS( SELECT TOP 2 cr.Nombre AS Carrera
				, COALESCE((p.Nombre+' '+p.SegundoNombre+' '+p.ApellidoPaterno+' '+p.ApellidoMaterno)
					,(p.Nombre+' '+p.ApellidoPaterno+' '+p.ApellidoMaterno)) AS 'Alumno'
				, NotaFinal AS Nota
					FROM Cursa c
						JOIN Persona p ON p.Codigo = c.CodigoAlumno
						JOIN Materia m ON m.Id = c.IdMateria
						JOIN Carrera cr ON cr.Id = m.IdCarrera
					ORDER BY Nota DESC
)
, pa AS( SELECT TOP 2 cr.Nombre AS Carrera
				, COALESCE((p.Nombre+' '+p.SegundoNombre+' '+p.ApellidoPaterno+' '+p.ApellidoMaterno)
					,(p.Nombre+' '+p.ApellidoPaterno+' '+p.ApellidoMaterno)) AS 'Alumno'
				, NotaFinal AS Nota
					FROM Cursa c
						JOIN Persona p ON p.Codigo = c.CodigoAlumno
						JOIN Materia m ON m.Id = c.IdMateria
						JOIN Carrera cr ON cr.Id = m.IdCarrera
					ORDER BY Nota
)

SELECT *
	,CASE WHEN Nota>50 THEN 'Aprobado' ELSE 'Reprobado' END AS 'Estado'
	FROM ma
	UNION
SELECT *
	,CASE WHEN Nota>50 THEN 'Aprobado' ELSE 'Reprobado' END AS 'Estado'
	FROM pa

/*
*
*	Tercera Pregunta
*	3. Crear una consulta con la materia que más  almunos reprobados tiene.
*/
;WITH mat AS(SELECT m.Nombre AS 'Materia', COUNT(*) AS 'Nro'
	FROM Cursa c
		JOIN Materia m
			ON m.Id=c.IdMateria
	WHERE c.NotaFinal<51
	GROUP BY m.Nombre
)
SELECT Materia
	FROM mat m
	WHERE m.Nro = (SELECT MAX(Nro) FROM mat m2)

/*
*
*	Cuarta Pregunta
*	4. Crear una consulta con la materia con la menor nota marcada por almuno
*/
SELECT m.Nombre
	FROM Cursa c
		JOIN Materia m 
			ON m.Id = c.IdMateria
	WHERE NotaFinal = (SELECT MIN(NotaFinal) FROM Cursa)
/*
*
*	Quinta Pregunta
*	5. Crear una consulta con los mejores almunos por semestre (1 almuno por cada carrera)
*/
SELECT cr.Nombre AS Carrera
	, COALESCE((p.Nombre+' '+p.SegundoNombre+' '+p.ApellidoPaterno+' '+p.ApellidoMaterno)
		,(p.Nombre+' '+p.ApellidoPaterno+' '+p.ApellidoMaterno)) AS 'Alumno'
	, NotaFinal AS Nota
		FROM Cursa c
			JOIN Persona p ON p.Codigo = c.CodigoAlumno
			JOIN Materia m ON m.Id = c.IdMateria
			JOIN Carrera cr ON cr.Id = m.IdCarrera
		WHERE c.NotaFinal = (SELECT MAX(c2.NotaFinal) 
							FROM Cursa c2 
							JOIN Materia m2 ON m2.Id = c2.IdMateria 
							WHERE m2.IdCarrera=m.IdCarrera and c2.Semestre = '0')
			and c.Semestre='0'
UNION
SELECT cr.Nombre AS Carrera
	, COALESCE((p.Nombre+' '+p.SegundoNombre+' '+p.ApellidoPaterno+' '+p.ApellidoMaterno)
		,(p.Nombre+' '+p.ApellidoPaterno+' '+p.ApellidoMaterno)) AS 'Alumno'
	, NotaFinal AS Nota
		FROM Cursa c
			JOIN Persona p ON p.Codigo = c.CodigoAlumno
			JOIN Materia m ON m.Id = c.IdMateria
			JOIN Carrera cr ON cr.Id = m.IdCarrera
		WHERE c.NotaFinal = (SELECT MAX(c2.NotaFinal) 
							FROM Cursa c2 
							JOIN Materia m2 ON m2.Id = c2.IdMateria 
							WHERE m2.IdCarrera=m.IdCarrera and c2.Semestre = '1')
			and c.Semestre='1'


/*
*
*	Sexta Pregunta
*	6. Realizar una cosulta de todos los almunos de la universidad paginado cada 10 filas 
*	(el usuario puede poner los valores para ver la pagina, por ejemplo si el usuario pone la pagina 3 deberá mostrar los alumnos del 31 al 40) Ordenados por apellido paterno.
*/

DECLARE @page int
SET @page = 1

;WITH ppl AS(SELECT ROW_NUMBER() OVER (ORDER BY ApellidoPaterno) AS row, ApellidoPaterno, ApellidoMaterno, Nombre, SegundoNombre
	FROM Persona
	ORDER BY ApellidoPaterno
)
SELECT *
	FROM ppl
	WHERE row BETWEEN (@page*10)-10 AND (@page*10)
